package com.yutel.template.dao.impl;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;

import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.support.JdbcDaoSupport;
import org.springframework.transaction.annotation.Transactional;

import com.yutel.template.dao.ITemplateDao;
import com.yutel.template.repository.Template;


@Transactional(rollbackFor=Exception.class)
public class TemplateDao extends JdbcDaoSupport implements ITemplateDao {

	public List<Template> tree(Template temp) {
		String sql="select id,"+temp.getTextColName()+" from "+temp.getTableName()+" where "+temp.getPidColName()+"="+temp.getId();
		if(temp.getId()==null || temp.getId()<1) {
			sql="select id,"+temp.getTextColName()+" from "+temp.getTableName()+" where "+temp.getPidColName()+" is null";
		}
		return getJdbcTemplate().query(sql,new TempTreeRowMapper(temp));
	}
	public int listCount(Template temp) {
		String sql="select count(1) from "+temp.getTableName()+" where "
				+temp.getPidColName()+"="+temp.getId();
		return getJdbcTemplate().queryForInt(sql);
	}
	public List<Map<String,Object>> list(Template temp) {
		StringBuffer sql=new StringBuffer();
		Iterator<String> it=temp.getColMap().keySet().iterator();
		while(it.hasNext()) {
			sql.append(it.next()).append(",");
		}
		if(sql.length()>0) {
			sql.deleteCharAt(sql.length()-1);
			sql.insert(0,"select ");
			sql.append(" from ").append(temp.getTableName());
		}
		sql.append(" where ").append(temp.getPidColName()).append("=").append(temp.getId());
		return getJdbcTemplate().query(sql.toString(),new TempListRowMapper(temp.getColMap()));
		
	}
	
	public List<Map<String,Object>> listByPID(Template temp,Map<String, String> map) {
		StringBuffer sql=new StringBuffer();
		Iterator it=map.keySet().iterator();
		while(it.hasNext()) {
			sql.append(it.next()).append(",");
		}
		if(sql.length()>0) {
			sql.deleteCharAt(sql.length()-1);
			sql.insert(0,"select ");
			sql.append(" from ").append(temp.getTableName());
		}
		if(temp.getId()>0) {
			sql.append(" where pid=").append(temp.getId());
		} else {
			sql.append(" where pid is null");
		}
		return getJdbcTemplate().query(sql.toString(),new TempListRowMapper(temp.getColMap()));
	}
	
	public void add(Template temp) {
		List<Object> paras=new ArrayList<Object>();
		StringBuffer sql=new StringBuffer();
		StringBuffer v=new StringBuffer();
		Iterator<String> it=temp.getColMap().keySet().iterator();
		while(it.hasNext()) {
			String el=(String)it.next();
			String ev=temp.getColMap().get(el);
			if("PID".equals(el) &&"0".equals(ev)) {
				continue;
			} else if("id".equals(el)){
				ev=Integer.toString(next(temp)+1);
			}
			sql.append(el).append(",");
			paras.add(ev);
			v.append("?,");
		}
		
		if(sql.length()>0) {
			sql.deleteCharAt(sql.length()-1);
			v.deleteCharAt(v.length()-1);
			sql.insert(0," (");
			sql.insert(0,temp.getTableName());
			sql.insert(0,"insert into ");
			sql.append(") values(").append(v.toString()).append(")");
		}
		getJdbcTemplate().update(sql.toString(),paras.toArray());
	}

	public void edit(Template temp) {
		List<Object> paras=new ArrayList<Object>();
		StringBuffer sql=new StringBuffer();
		Iterator<String> it=temp.getColMap().keySet().iterator();
		while(it.hasNext()) {
			String el=(String)it.next();
			String ev=temp.getColMap().get(el);
			if("PID".equals(el) &&("".equals(ev) || "0".equals(ev))) {
				continue;
			}
			paras.add(ev);
			sql.append(el).append("=?,");
		}
		if(sql.length()>0) {
			sql.deleteCharAt(sql.length()-1);
			sql.insert(0," set ");
			sql.insert(0,temp.getTableName());
			sql.insert(0,"update ");
			sql.append(" where id=").append(temp.getId());
		}
		getJdbcTemplate().update(sql.toString(),paras.toArray());
	}
	public Map view(Template temp) {
		String sql="select * from "+temp.getTableName()+" where id=?";
		return getJdbcTemplate().queryForMap(sql.toString(),new Object[]{temp.getId()});
	}
	public void remove(Template temp) {
		String sql="delete from "+temp.getTableName()+" where id="+temp.getId();
		getJdbcTemplate().update(sql);
	}
	private int next(Template temp) {
		String sql="select max(id) from "+temp.getTableName();
		return getJdbcTemplate().queryForInt(sql);
	}
	private class TempListRowMapper implements RowMapper {
		Map<String,String> cols;
		public TempListRowMapper(Map<String,String> map){
			this.cols=map;
		}
		public Object mapRow(ResultSet rs, int index) throws SQLException {
			Map<String,Object> row=new HashMap<String,Object>();
			Iterator<String> it=cols.keySet().iterator();
			while(it.hasNext()) {
				String col=it.next();
				String colType=(String)cols.get(col);
				if("int".equals(colType)) {
					row.put(col, rs.getInt(col));
				}else {
					row.put(col, rs.getString(col));
				}
			}
			return row;
		}
	}
	private class TempTreeRowMapper implements RowMapper {
		private Template temp;
		public TempTreeRowMapper(Template temp){
			this.temp=temp;
		}
		public Object mapRow(ResultSet rs, int index) throws SQLException {
			Template tree = new Template();
			tree.setId(rs.getInt("id"));
			tree.setText(rs.getString(temp.getTextColName()));
			return tree;
		}
	}
}
